IF OBJECT_ID('dbo.GetSubContractGroupByCodes') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.GetSubContractGroupByCodes
    IF OBJECT_ID('dbo.GetSubContractGroupByCodes') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.GetSubContractGroupByCodes >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.GetSubContractGroupByCodes >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.GetSubContractGroupByCodes
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 10/13/2008
--
-- Description: Returns list of Group by Codes by Report...
--
-- Parameters:  None
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.GetSubContractGroupByCodes('TYPE')
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 10/13/08    KC        Created
-- 11/13/08    KC        Added SubContractor
-- 12/16/08    KC        Added RM Staff Member for both reports
-- 03/18/09    RM	 Add Group By values for Sub Activity Report
--
-- =======================================================================
CREATE PROCEDURE dbo.GetSubContractGroupByCodes 
     (
     @Report                  VARCHAR(100),
     @IncludeSubContr         VARCHAR(10) = NULL
     )
AS
BEGIN
     /*  Returned Fields for Dropdown
     --------------------------------
     GroupByCode           VARCHAR 
     GroupByName           VARCHAR
     */

     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error            INT,
               @RowCount         INT

     IF @Report = 'RptSubContractWrapperReport'
     BEGIN
          IF UPPER(@IncludeSubContr) = 'YES'
               SELECT    'ORG' AS GroupByCode,
                         'Organizations' AS GroupByName
               UNION
               SELECT    'SS',
                         'RM Staff Member'
			   UNION     
			   SELECT    'CM',  
                         'Contract Manager' 
               UNION     
               SELECT    'SC',
                         'SubContractor'
               ORDER BY GroupByCode
          ELSE
               SELECT    'ORG' AS GroupByCode,
                         'Organizations' AS GroupByName
               UNION
               SELECT    'SS',
                         'RM Staff Member'
               ORDER BY GroupByName
          
          -- Return Success
          RETURN 0
     END  

     IF @Report = 'RptSubContractActivityWrapperReport'
     BEGIN
          IF UPPER(@IncludeSubContr) = 'YES'
               SELECT    'ORG' AS GroupByCode,
                         'Organizations' AS GroupByName
               --UNION
               --SELECT    'AB',
               --          'Activity By'
	           UNION
               SELECT    'SS',
                         'RM Staff Member'
			   UNION     
			   SELECT    'CM',  
                         'Contract Manager' 
               
               UNION     
               SELECT    'SC',
                         'SubContractor'
               ORDER BY GroupByCode	
          ELSE
               SELECT    'ORG' AS GroupByCode,
                         'Organizations' AS GroupByName
               --UNION
               --SELECT    'AB',
               --          'Activity By'
               UNION
               SELECT    'SS',
                         'RM Staff Member'
               ORDER BY GroupByName
          
          -- Return Success
          RETURN 0
     END 

     -- Default if we got here:
     SELECT    'ERR' AS GroupByCode,
               'Error: ReportNotSetup' AS GroupByName
     
     -- Return Success
     RETURN -1
     
END
go

IF OBJECT_ID('dbo.GetSubContractGroupByCodes') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.GetSubContractGroupByCodes >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.GetSubContractGroupByCodes >>>'
go

GRANT EXECUTE ON dbo.GetSubContractGroupByCodes TO InsightReport,Insight
go
